#!/bin/bash
APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi

dws_trade_province_order_nd="
insert overwrite table ${APP}.dws_trade_province_order_nd partition(dt='$do_date')
select
    province_id,
    province_name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)),
    sum(order_count_1d),
    sum(order_original_amount_1d),
    sum(coupon_reduce_amount_1d),
    sum(order_total_amount_1d)
from ${APP}.dws_trade_province_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by province_id,province_name,region_id,area_code,iso_code,iso_3166_2;
"

dws_trade_user_payment_nd="
insert overwrite table ${APP}.dws_trade_user_payment_nd partition(dt='$do_date')
select
    user_id,
    sum(if(dt>=date_add('$do_date',-6),payment_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),payment_num_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),payment_amount_1d,0)),
    sum(payment_count_1d),
    sum(payment_num_1d),
    sum(payment_amount_1d)
from ${APP}.dws_trade_user_payment_1d
where dt>=date_add('$do_date',-29)
group by  user_id;
"

dws_process_user_chapter_nd="
insert overwrite table ${APP}.dws_process_user_chapter_nd partition(dt='$do_date')
select
    chapter_id,
    chapter_name,
    sum(if(dt>=date_add('$do_date',-6),video_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),video_sec_sum_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),video_user_count_1d,0)),
    sum(video_count_1d),
    sum(video_sec_sum_1d),
    sum(video_user_count_1d)
from ${APP}.dws_process_user_chapter_1d
where dt>=date_add('$do_date',-29)
group by  chapter_id,chapter_name;
"

dws_process_user_course_nd="
insert overwrite table ${APP}.dws_process_user_course_nd partition(dt='$do_date')
select
    course_id,
    course_name,
    sum(if(dt>=date_add('$do_date',-6),video_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),video_sec_sum_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),video_user_count_1d,0)),
    sum(video_count_1d),
    sum(video_sec_sum_1d),
    sum(video_user_count_1d)
from ${APP}.dws_process_user_course_1d
where dt>=date_add('$do_date',-29)
group by  course_id,course_name;
"

dws_trade_user_cart_add_nd="
insert overwrite table ${APP}.dws_trade_user_cart_add_nd partition(dt='$do_date')
select
    user_id,
    sum(if(dt>=date_add('$do_date',-6),cart_add_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),cart_add_num_1d,0)),
    sum(cart_add_count_1d),
    sum(cart_add_num_1d)
from ${APP}.dws_trade_user_cart_add_1d
where dt>=date_add('$do_date',-29)
group by  user_id;
"

dws_trade_user_order_nd="
insert overwrite table ${APP}.dws_trade_user_order_nd partition(dt='$do_date')
select
    user_id,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_num_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),coupon_reduce_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),final_total_amount_1d,0)),
    sum(order_count_1d),
    sum(order_num_1d),
    sum(order_original_amount_1d),
    sum(coupon_reduce_amount_1d),
    sum(final_total_amount_1d)
from ${APP}.dws_trade_user_order_1d
where dt>=date_add('$do_date',-29)
group by  user_id;
"

dws_traffic_page_visitor_page_view_nd="
insert overwrite table ${APP}.dws_traffic_page_visitor_page_view_nd partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(if(dt>=date_add('$do_date',-6),during_time_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),view_count_1d,0)),
    sum(during_time_1d),
    sum(view_count_1d)
from ${APP}.dws_traffic_page_visitor_page_view_1d
where dt>=date_add('$do_date',-29)
group by  mid_id,brand,model,operate_system,page_id;
"

dws_traffic_session_page_view_nd="
insert overwrite table ${APP}.dws_traffic_session_page_view_nd partition(dt='$do_date')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    channel,
    sum(if(dt>=date_add('$do_date',-6),during_time_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),page_count_1d,0)),
    sum(during_time_1d),
    sum(page_count_1d)
from ${APP}.dws_traffic_session_page_view_1d
where dt>=date_add('$do_date',-29)
group by  session_id,mid_id,brand,model,operate_system,version_code,channel;
"

case $1 in
    "dws_trade_province_order_nd" )
        hive -e "$dws_trade_province_order_nd"
    ;;
    "dws_trade_user_payment_nd" )
        hive -e "$dws_trade_user_payment_nd"
    ;;
    "dws_process_user_chapter_nd" )
        hive -e "dws_process_user_chapter_nd"
    ;;
    "dws_process_user_course_nd" )
        hive -e "dws_process_user_course_nd"
    ;;
    "dws_trade_user_cart_add_nd" )
        hive -e "dws_trade_user_cart_add_nd"
    ;;
    "dws_trade_user_order_nd" )
        hive -e "dws_trade_user_order_nd"
    ;;
    "dws_traffic_page_visitor_page_view_nd" )
        hive -e "dws_traffic_page_visitor_page_view_nd"
    ;;
    "dws_traffic_session_page_view_nd" )
        hive -e "dws_traffic_session_page_view_nd"
    ;;
    "all" )
        hive -e "$dws_trade_province_order_nd$dws_trade_user_payment_nd$dws_process_user_chapter_nd$dws_process_user_course_nd$dws_trade_user_cart_add_nd
$dws_trade_user_order_nd$dws_traffic_page_visitor_page_view_nd$dws_traffic_session_page_view_nd"
    ;;
esac


